package com.zhenyi.liuxue.admin;

import java.io.File;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.xerces.util.SynchronizedSymbolTable;

import com.cjw.model.DBMap;
import com.jfinal.kit.JsonKit;
import com.jfinal.kit.Kv;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.SqlPara;
import com.liuxue.common.model.LxActivity;
import com.liuxue.common.model.LxCertificate;
import com.liuxue.common.model.LxCnSchool;
import com.liuxue.common.model.LxCompany;
import com.liuxue.common.model.LxMajor;
import com.liuxue.common.model.LxSchool;
import com.liuxue.common.model.LxUkSchool;
import com.liuxue.common.model.LxUser;
import com.liuxue.common.model.base.BaseLxMajor;
import com.utils.PathUtils;
import com.youqu.common.model.YqCategory;
import com.zhenyi.core.RowHandler;

import cn.hutool.core.util.NumberUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;

public class StatisticController extends AdminController {

	public void schoolVisit() {
		String startTime = getPara("startTime");
		String endTime = getPara("endTime");
		if (StrKit.notBlank(startTime) && StrKit.notBlank(endTime)) {
			setAttr("startTime", startTime);
			setAttr("endTime", endTime);
		}

		SqlPara findSchoolSqlPara = Db.use("liuxue").getSqlPara("LXAdminFindSchools");
		List<LxSchool> schoolList = LxSchool.dao.find(findSchoolSqlPara);
		ArrayList<DBMap> schoolStatList = new ArrayList<>();

		SqlPara sp = Db.use("liuxue").getSqlPara("LXSubscribeCount");
		List<Record> subscribeCounts = Db.use("liuxue").find(sp);

		for (LxSchool school : schoolList) {
			for (Record record : subscribeCounts) {
				if (record.getLong("schoolId") == school.getId()) {
					school.put("subscribes", record.getLong("count"));
				}
			}

			DBMap map = new DBMap();
			Kv kv1 = Kv.create();
			kv1.set("schoolId", school.getId());
			kv1.set("type", 1);
			if (StrKit.notBlank(startTime) && StrKit.notBlank(endTime)) {
				kv1.set("startTime", startTime + " 00:00:00");
				kv1.set("endTime", endTime + " 23:59:59");
			}
			SqlPara sqlPara1 = Db.use("liuxue").getSqlPara("LXSchoolVisitCount", kv1);
			Long schoolVisit = Db.use("liuxue").findFirst(sqlPara1).getLong("count");
			map.put("schoolVisit", schoolVisit);

			Kv kv2 = Kv.create();
			kv2.set("schoolId", school.getId());
			kv2.set("type", 2);
			if (StrKit.notBlank(startTime) && StrKit.notBlank(endTime)) {
				kv2.set("startTime", startTime + " 00:00:00");
				kv2.set("endTime", endTime + " 23:59:59");
			}
			SqlPara sqlPara2 = Db.use("liuxue").getSqlPara("LXSchoolVisitCount", kv2);
			Long majorVisit = Db.use("liuxue").findFirst(sqlPara2).getLong("count");
			map.put("majorVisit", majorVisit);

			Kv kv3 = Kv.create();
			kv3.set("schoolId", school.getId());
			kv3.set("type", 3);
			if (StrKit.notBlank(startTime) && StrKit.notBlank(endTime)) {
				kv3.set("startTime", startTime + " 00:00:00");
				kv3.set("endTime", endTime + " 23:59:59");
			}
			SqlPara sqlPara3 = Db.use("liuxue").getSqlPara("LXSchoolVisitCount", kv3);
			Long policyVisit = Db.use("liuxue").findFirst(sqlPara3).getLong("count");
			map.put("policyVisit", policyVisit);

			Kv kv4 = Kv.create();
			kv4.set("schoolId", school.getId());
			kv4.set("type", 4);
			if (StrKit.notBlank(startTime) && StrKit.notBlank(endTime)) {
				kv4.set("startTime", startTime + " 00:00:00");
				kv4.set("endTime", endTime + " 23:59:59");
			}
			SqlPara sqlPara4 = Db.use("liuxue").getSqlPara("LXSchoolVisitCount", kv4);
			Long questionVisit = Db.use("liuxue").findFirst(sqlPara4).getLong("count");
			map.put("questionVisit", questionVisit);

			map.put("school", school);
			schoolStatList.add(map);
		}

		setInfoItem(JsonKit.toJson(schoolStatList));
		render("/lx/admin/statistic/schoolVisit.html");
	}

	public void majorVisit() {
		Long schoolId = getParaToLong("schoolId");
		String startTime = getPara("startTime");
		String endTime = getPara("endTime");
		Integer asc = getParaToInt("asc");

		Kv kv = Kv.create();
		if (schoolId != null) {
			kv.set("schoolId", schoolId);
			setAttr("schoolId", schoolId);
		}
		if (StrKit.notBlank(startTime) && StrKit.notBlank(endTime)) {
			kv.set("startTime", startTime + " 00:00:00");
			kv.set("endTime", endTime + " 23:59:59");
			setAttr("startTime", startTime);
			setAttr("endTime", endTime);
		}
		if (asc != null) {
			kv.set("asc", asc);
			setAttr("asc", asc);
		}
		SqlPara sqlPara = Db.use("liuxue").getSqlPara("LXMajorVisitCount", kv);
		if (isDownload()) {
			List<Record> items = db.find(sqlPara);
			ArrayList<Map<String, Object>> rows = new ArrayList<>();
			for (Record record : items) {
				Map<String, Object> row1 = new LinkedHashMap<>();
				row1.put("课程", record.get("name"));
				row1.put("大学", record.get("schoolName"));
				row1.put("访问数", record.get("count"));
				rows.add(row1);
			}
			String path = PathUtils.getFile("data", "superlink", "课程统计.xlsx");
			writeExcel(rows, path);
			return;
		}
		Page<Record> pages = Db.use("liuxue").paginate(getPage(), getPageSize(), sqlPara);

		setRainbow(pages);
		setItems(JsonKit.toJson(pages.getList()));
		setAttr("schools", JsonKit.toJson(LxSchool.dao.findAll()));
		render("/lx/admin/statistic/majorVisit.html");
	}

	private void writeExcel(ArrayList<Map<String, Object>> rows, String path) {
		// 通过工具类创建writer
		ExcelWriter writer = ExcelUtil.getWriter(path);
		// 合并单元格后的标题行，使用默认标题样式
		// 一次性写出内容，使用默认样式，强制输出标题
		writer.write(rows);
		// 关闭writer，释放内存
		writer.close();
		renderFile(new File(path));
	}

	public void register() {
		String startTime = getPara("startTime");
		String endTime = getPara("endTime");
		Long companyId = getParaToLong("companyId");
		String province = getPara("province");
		String city = getPara("city");
		Integer countBy = getParaToInt("countBy", 0); // 0通过省市 1公司 2性别
		Integer order = getParaToInt("order", 0); // 0 DESC 1 ASC

		Kv kv = Kv.create();
		if (StrKit.notBlank(startTime) && StrKit.notBlank(endTime)) {
			kv.set("startTime", startTime + " 00:00:00");
			kv.set("endTime", endTime + " 23:59:59");
			setAttr("startTime", startTime);
			setAttr("endTime", endTime);
		}
		if (companyId != null) {
			kv.set("companyId", companyId);
			setAttr("companyId", companyId);
		}
		if (StrKit.notBlank(province)) {
			kv.set("province", province);
			setAttr("province", province);
		}
		if (StrKit.notBlank(city)) {
			kv.set("city", city);
			setAttr("city", city);
		}
		kv.set("order", order);

		SqlPara totalSP = Db.use("liuxue").getSqlPara("LXUserCount", kv);
		Long total = Db.use("liuxue").findFirst(totalSP).getLong("count");

		if (countBy == 0) {
			SqlPara sqlPara = Db.use("liuxue").getSqlPara("LXUserCountByProvince", kv);
			// List<Record> list = Db.use("liuxue").find(sqlPara);
			// ArrayList<String> provinceLegendArray = new ArrayList<>();
			// ArrayList<DBMap> provinceSeriesArray = new ArrayList<>();
			// for (Record record : list) {
			// if (StrKit.notBlank(city)) {
			// provinceLegendArray.add(record.getStr("city"));
			// DBMap map = new DBMap();
			// map.put("name", record.getStr("city"));
			// map.put("value", record.getLong("count"));
			// provinceSeriesArray.add(map);
			// } else {
			// provinceLegendArray.add(record.getStr("province"));
			// DBMap map = new DBMap();
			// map.put("name", record.getStr("province"));
			// map.put("value", record.getLong("count"));
			// provinceSeriesArray.add(map);
			// }
			// }
			// DBMap provinceData = new DBMap();
			// provinceData.put("legendData", provinceLegendArray);
			// provinceData.put("seriesData", provinceSeriesArray);
			if (isDownload()) {
				List<Record> items = db.find(sqlPara);
				ArrayList<Map<String, Object>> rows = new ArrayList<>();
				for (Record record : items) {
					Map<String, Object> row1 = new LinkedHashMap<>();
					row1.put("省市", StrKit.notBlank(city) ? record.get("city") : record.get("province"));
					row1.put("人数", record.get("count"));
					row1.put("占比%",
							NumberUtil.div(record.getLong("count"), total, 4).multiply(BigDecimal.valueOf(100)));
					rows.add(row1);
				}
				String path = PathUtils.getFile("data", "superlink", "注册统计_省市.xlsx");
				writeExcel(rows, path);
				return;
			}
			Page<Record> pages = Db.use("liuxue").paginate(getPage(), getPageSize(), sqlPara);
			for (Record record : pages.getList()) {
				record.set("percentage",
						NumberUtil.div(record.getLong("count"), total, 4).multiply(BigDecimal.valueOf(100)));
				if (StrKit.notBlank(city)) {
					record.set("province", record.getStr("city"));
				}
			}
			setRainbow(pages);
			setItems(JsonKit.toJson(pages.getList()));

		} else if (countBy == 1) {
			SqlPara sqlPara2 = Db.use("liuxue").getSqlPara("LXUserCountByCompany", kv);
			// List<Record> list2 = Db.use("liuxue").find(sqlPara2);
			// ArrayList<String> companyLegendArray = new ArrayList<>();
			// ArrayList<DBMap> companySeriesArray = new ArrayList<>();
			// Long otherCount = 0L;
			// for (int i = 0; i < list2.size(); i++) {
			// Record record = list2.get(i);
			// if (i > 25) {
			// otherCount += record.getLong("count");
			// } else {
			// companyLegendArray.add(record.getStr("name"));
			// DBMap map = new DBMap();
			// map.put("name", record.getStr("name"));
			// map.put("value", record.getLong("count"));
			// companySeriesArray.add(map);
			// }
			// }
			// if (otherCount != 0) {
			// DBMap other = new DBMap();
			// other.put("name", "其他");
			// other.put("value", otherCount);
			// companyLegendArray.add("其他");
			// companySeriesArray.add(other);
			// }
			// DBMap companyData = new DBMap();
			// companyData.put("legendData", companyLegendArray);
			// companyData.put("seriesData", companySeriesArray);
			if (isDownload()) {
				List<Record> items = db.find(sqlPara2);
				ArrayList<Map<String, Object>> rows = new ArrayList<>();
				for (Record record : items) {
					Map<String, Object> row1 = new LinkedHashMap<>();
					row1.put("公司", record.get("name"));
					row1.put("人数", record.get("count"));
					row1.put("占比%",
							NumberUtil.div(record.getLong("count"), total, 4).multiply(BigDecimal.valueOf(100)));
					rows.add(row1);
				}
				String path = PathUtils.getFile("data", "superlink", "注册统计_公司.xlsx");
				writeExcel(rows, path);
				return;
			}
			Page<Record> pages = Db.use("liuxue").paginate(getPage(), getPageSize(), sqlPara2);
			for (Record record : pages.getList()) {
				record.set("percentage",
						NumberUtil.div(record.getLong("count"), total, 4).multiply(BigDecimal.valueOf(100)));
			}
			setRainbow(pages);
			setItems(JsonKit.toJson(pages.getList()));

		} else if (countBy == 2) {
			SqlPara sqlPara3 = Db.use("liuxue").getSqlPara("LXUserCountBySex", kv);
			// List<Record> list3 = Db.use("liuxue").find(sqlPara3);
			// ArrayList<String> sexLegendArray = new ArrayList<>();
			// ArrayList<DBMap> sexSeriesArray = new ArrayList<>();
			// for (Record record : list3) {
			// sexLegendArray.add(record.getInt("sex") == 0 ? "男" : "女");
			// DBMap map = new DBMap();
			// map.put("name", record.getInt("sex") == 0 ? "男" : "女");
			// map.put("value", record.getLong("count"));
			// sexSeriesArray.add(map);
			// }
			// DBMap sexData = new DBMap();
			// sexData.put("legendData", sexLegendArray);
			// sexData.put("seriesData", sexSeriesArray);

			// DBMap result = new DBMap();
			// result.put("provinceData", provinceData);
			// result.put("companyData", companyData);
			// result.put("sexData", sexData);
			// setInfoItem(JsonKit.toJson(result));
			if (isDownload()) {
				List<Record> items = db.find(sqlPara3);
				ArrayList<Map<String, Object>> rows = new ArrayList<>();
				for (Record record : items) {
					Map<String, Object> row1 = new LinkedHashMap<>();
					row1.put("性别", record.getInt("sex") == 0 ? "男" : "女");
					row1.put("人数", record.get("count"));
					row1.put("占比%",
							NumberUtil.div(record.getLong("count"), total, 4).multiply(BigDecimal.valueOf(100)));
					rows.add(row1);
				}
				String path = PathUtils.getFile("data", "superlink", "注册统计_性别.xlsx");
				writeExcel(rows, path);
				return;
			}
			Page<Record> pages = Db.use("liuxue").paginate(getPage(), getPageSize(), sqlPara3);
			for (Record record : pages.getList()) {
				record.set("percentage",
						NumberUtil.div(record.getLong("count"), total, 4).multiply(BigDecimal.valueOf(100)));
			}
			setRainbow(pages);
			setItems(JsonKit.toJson(pages.getList()));
		}
		setAttr("countBy", countBy);
		setAttr("order", order);
		setAttr("companies", JsonKit.toJson(LxCompany.dao.findAll()));
		render("/lx/admin/statistic/register.html");
	}

	public void activity() {
		String startTime = getPara("startTime");
		String endTime = getPara("endTime");
		Kv kv = Kv.create();
		if (StrKit.notBlank(startTime) && StrKit.notBlank(endTime)) {
			kv.set("startTime", startTime + " 00:00:00");
			kv.set("endTime", endTime + " 23:59:59");
			setAttr("startTime", startTime);
			setAttr("endTime", endTime);
		}
		SqlPara sqlPara = Db.use("liuxue").getSqlPara("LXAdminFindActivity", kv);
		Page<LxActivity> pages = LxActivity.dao.paginate(getPage(), getPageSize(), sqlPara);
		setRainbow(pages);
		setItems(JsonKit.toJson(pages.getList()));
		render("/lx/admin/statistic/activity.html");
	}

	public void activityStatPro() {
		String keyword = getPara("keyword");
		// String location = getPara("location");
		String province = getPara("province");
		String city = getPara("city");
		Long companyId = getParaToLong("companyId");
		Long schoolId = getParaToLong("schoolId");
		Kv kv = Kv.create();
		if (StrKit.notBlank(keyword)) {
			kv.set("keyword", keyword);
			setAttr("keyword", keyword);
		}
		// if (StrKit.notBlank(location)) {
		// kv.set("location", location);
		// setAttr("location", location);
		// }
		if (StrKit.notBlank(province)) {
			kv.set("province", province);
			setAttr("province", province);
		}
		if (StrKit.notBlank(city)) {
			kv.set("city", city);
			setAttr("city", city);
		}
		if (companyId != null) {
			kv.set("companyId", companyId);
			setAttr("companyId", companyId);
		}
		if (schoolId != null) {
			kv.set("schoolId", schoolId);
			setAttr("schoolId", schoolId);
		}

		Long activityId = getParaToLong("activityId");
		if (activityId != null) {
			kv.set("activityId", activityId);
			setAttr("activityId", activityId);
		}
		if (schoolId != null) {
			kv.set("schoolId", schoolId);
			setAttr("schoolId", schoolId);
		}

		SqlPara sqlPara = Db.use("liuxue").getSqlPara("LXAdminFindUserWithActivity", kv);

		Page<LxUser> pages = LxUser.dao.paginate(getPage(), getPageSize(), sqlPara);
		List<LxUser> list = pages.getList();
		for (LxUser lxUser : list) {
			Kv userKv = Kv.by("userId", lxUser.getId());

			SqlPara userActivityPara = db.getSqlPara("LXStatisticUserActivity", userKv);
			List<Record> records = db.find(userActivityPara);
			lxUser.put("activity", records);
		}
		SqlPara sqlPara2 = Db.use("liuxue").getSqlPara("LXFindCompanies");
		List<LxCompany> companies = LxCompany.dao.find(sqlPara2);

		SqlPara activitySqlPara = db.getSqlPara("LXAdminFindActivity");
		List<LxActivity> activities = LxActivity.dao.find(activitySqlPara);

		SqlPara schoolsqlPara = Db.use("liuxue").getSqlPara("LXAdminFindUkSchool");
		List<LxUkSchool> schools = LxUkSchool.dao.find(schoolsqlPara);

		setRainbow(pages);
		setItems(JsonKit.toJson(list));
		setAttr("companies", JsonKit.toJson(companies));
		setAttr("activities", JsonKit.toJson(activities));
		setAttr("schools", JsonKit.toJson(schools));

		render("/lx/admin/statistic/activityStatPro.html");

	}

	public void activityStat() {
		Long activityId = getParaToLong("activityId");
		if (activityId == null) {
			renderFailure(500, "activity id is null");
			return;
		}
		LxActivity activity = LxActivity.dao.findById(activityId);
		if (activity == null) {
			renderFailure(404, "not found");
			return;
		}

		DBMap result = new DBMap();
		result.put("activity", activity);
		Kv kv = Kv.create();
		kv.set("activityId", activityId);
		SqlPara sqlPara = Db.use("liuxue").getSqlPara("LXActivityUserCount", kv);
		List<Record> provinceData = Db.use("liuxue").find(sqlPara); // 按省份统计
		result.put("provinceData", provinceData);

		kv.set("byCompany", 1);
		SqlPara sqlPara2 = Db.use("liuxue").getSqlPara("LXActivityUserCount", kv);
		List<Record> companyData = Db.use("liuxue").find(sqlPara2); // 按公司统计
		result.put("companyData", companyData);

		setInfoItem(JsonKit.toJson(result));
		render("/lx/admin/statistic/activityStat.html");
	}

	public void certStat() {
		String startTime = getPara("startTime");
		String endTime = getPara("endTime");
		Long schoolId = getParaToLong("schoolId");
		Long companyId = getParaToLong("companyId");
		Long certId = getParaToLong("certId");
		Integer status = getParaToInt("status");
		String province = getPara("province");
		String city = getPara("city");

		Kv kv = Kv.create();
		if (StrKit.notBlank(startTime) && StrKit.notBlank(endTime)) {
			String start = startTime + " 00:00:00";
			String end = endTime + " 23:59:59";
			kv.set("startTime", start);
			kv.set("endTime", end);
			setAttr("startTime", startTime);
			setAttr("endTime", endTime);
		}
		if (schoolId != null) {
			kv.set("schoolId", schoolId);
			setAttr("schoolId", schoolId);
		}
		if (companyId != null) {
			kv.set("companyId", companyId);
			setAttr("companyId", companyId);
		}
		if (certId != null) {
			kv.set("certId", certId);
			setAttr("certId", certId);
		}
		if (StrKit.notBlank(province)) {
			kv.set("province", province);
			setAttr("province", province);
		}
		if (StrKit.notBlank(city)) {
			kv.set("city", city);
			setAttr("city", city);
		}
		if (status != null) {
			kv.set("status", status);
			setAttr("status", status);
		}
		SqlPara sqlPara = Db.use("liuxue").getSqlPara("LXAdminCertStatistic", kv);
		if (isDownload()) {
			String path = PathUtils.getFile("data", "superlink", "证书信息统计.xlsx");
			genExcel(db.find(sqlPara), path, new RowHandler() {

				@Override
				public Map<String, Object> onRow(Record record, Map<String, Object> row) {
					row.put("姓名", record.get("userName"));
					row.put("大学", record.get("schoolName"));
					row.put("证书名称", record.get("certTitle"));
					row.put("省份", record.get("province"));
					row.put("城市", record.get("city"));
					row.put("公司名", record.get("companyName"));
					row.put("最后学习时间", record.get("lastStudyTime"));
					row.put("最后考试时间", record.get("lastExamTime"));
					row.put("培训状态", record.get("status").equals("1") ? "已获得证书" : "学习中");
					row.put("获得证书时间", record.get("passTime"));
					return row;
				}
			});
			return;
		}
		Page<Record> pages = Db.use("liuxue").paginate(getPage(), getPageSize(), sqlPara);
		setRainbow(pages);
		setItems(JsonKit.toJson(pages.getList()));
		setAttr("schools", JsonKit.toJson(LxSchool.dao.findAll()));
		setAttr("companies", JsonKit.toJson(LxCompany.dao.findAll()));
		SqlPara spCert = Db.use("liuxue").getSqlPara("LXAdminFindCertificate", kv);
		setAttr("certs", JsonKit.toJson(LxCertificate.dao.find(spCert)));
		render("/lx/admin/statistic/certStat.html");
	}

	/**
	 * 政策访问统计
	 */
	public void policyStat() {
		Kv kv = Kv.create();
		Long schoolId = getParaToLong("schoolId");
		if (schoolId != null) {
			kv.set("schoolId", schoolId);
			setAttr("schoolId", schoolId);
		}
		SqlPara sqlPara = db.getSqlPara("LXAdminPolicyStatistic", kv);
		SqlPara sqlPara2 = db.getSqlPara("LXAdminPolicyDownloadCount", kv);
		if (isDownload()) {
			List<Record> items = db.find(sqlPara);
			List<Record> downloadList = db.find(sqlPara2);

			for (Record record : downloadList) {
				for (int i = 0; i < items.size(); i++) {
					if (items.get(i).getLong("id").equals(record.getLong("id"))) {
						items.get(i).set("download", record.getLong("download"));
					}
				}
			}
			ArrayList<Map<String, Object>> rows = new ArrayList<>();
			for (Record record : items) {
				Map<String, Object> row1 = new LinkedHashMap<>();
				row1.put("大学", record.get("name"));
				row1.put("政策", record.get("title"));
				row1.put("访问数", record.get("visit"));
				row1.put("附件下载次数", record.get("download"));
				rows.add(row1);
			}
			String path = PathUtils.getFile("data", "superlink", "政策统计.xlsx");
			writeExcel(rows, path);
			return;
		}
		Page<Record> pages = db.paginate(getPage(), PAGE_SIZE, sqlPara);
		List<Record> downloadList = db.find(sqlPara2);

		List<Record> result = pages.getList();
		for (Record record : downloadList) {
			for (int i = 0; i < result.size(); i++) {
				if (result.get(i).getLong("id").equals(record.getLong("id"))) {
					result.get(i).set("download", record.getLong("download"));
				}
			}
		}

		setRainbow(pages);
		setAttr("schools", JsonKit.toJson(LxSchool.dao.findAll()));
		setData("items", result);
		render("/lx/admin/statistic/policyStat.html");
	}

	/**
	 * 用户关注的大学
	 */
	public void subscribeStat() {
		Long userId = getParaToLong("userId");
		if (userId == null) {
			renderError(404);
			return;
		}
		Kv kv = Kv.by("userId", userId);
		kv.set("userId", userId);
		setAttr("userId", userId);
		SqlPara sqlPara = db.getSqlPara("LXAdminUserSubscribeStatistic", kv);
		Page<Record> pages = db.paginate(getPage(), PAGE_SIZE, sqlPara);
		setRainbow(pages);
		setData("items", pages.getList());
		render("/lx/admin/statistic/subscribeStat.html");
	}

	/**
	 * 每个大学关注的用户
	 */
	public void subscribeSchoolStat() {
		Long schoolId = getParaToLong("schoolId");
		if (schoolId == null) {
			renderError(404);
			return;
		}
		Kv kv = Kv.by("schoolId", schoolId);
		kv.set("schoolId", schoolId);
		setAttr("schoolId", schoolId);
		SqlPara sqlPara = db.getSqlPara("LXAdminUserSubscribeSchoolStatistic", kv);
		if (isDownload()) {
			List<Record> items = db.find(sqlPara);
			ArrayList<Map<String, Object>> rows = new ArrayList<>();
			for (Record record : items) {
				Map<String, Object> row1 = new LinkedHashMap<>();
				row1.put("大学", record.get("schoolName"));
				row1.put("用户", record.get("name"));
				row1.put("微信", record.get("wechatNickname"));
				row1.put("关注时间", record.get("addTime"));
				rows.add(row1);
			}
			String path = PathUtils.getFile("data", "superlink", "关注统计.xlsx");
			writeExcel(rows, path);
			return;
		}
		Page<Record> pages = db.paginate(getPage(), PAGE_SIZE, sqlPara);
		setRainbow(pages);
		setData("items", pages.getList());
		setAttr("schools", JsonKit.toJson(LxSchool.dao.findAll()));
		render("/lx/admin/statistic/subscribeSchoolStat.html");
	}

	public void searchStat() {
		String startTime = getPara("startTime");
		String endTime = getPara("endTime");
		String cnSchoolId = getPara("cnSchoolId");
		String departmentId = getPara("departmentId");
		if (StrKit.notBlank(startTime) && StrKit.notBlank(endTime)) {
			setAttr("startTime", startTime);
			setAttr("endTime", endTime);
		}
		Kv kv = Kv.create();
		if (StrKit.notBlank(startTime) && StrKit.notBlank(endTime)) {
			kv.set("startTime", startTime + " 00:00:00");
			kv.set("endTime", endTime + " 23:59:59");
		}
		if (cnSchoolId != null) {
			kv.set("cnSchoolId", cnSchoolId);
			setAttr("cnSchoolId", cnSchoolId);
		}
		if (departmentId != null) {
			kv.set("departmentId", departmentId);
			setAttr("departmentId", departmentId);
		}
		SqlPara sqlPara = db.getSqlPara("LXAdminSearchCount", kv);
		Record record = db.findFirst(sqlPara);
		Long searchCount = record.getLong("count");

		SqlPara sP1 = db.getSqlPara("LXFindAllcnSchools");
		List<LxCnSchool> Cnschools = LxCnSchool.dao.find(sP1);
		setData("Cnschools", Cnschools);

		SqlPara sP = db.getSqlPara("LXFindAllMajors");
		List<LxMajor> departments = LxMajor.dao.find(sP);
		setData("departments", departments);

		setAttr("searchCount", searchCount);
		render("/lx/admin/statistic/searchStat.html");
	}
}
